from django.shortcuts import render, redirect, HttpResponse
from utils.sql_helper import exec_sql


def students(request):
    student_list = exec_sql("""
                            select student.id, student.sname, class.cname 
                            from student left join class on student.class_id = class.id
                            """,  is_get_list=True)
    return render(request, "student/students.html", {"student_list":student_list})


def add_student(request):
    if request.method == "GET":
        class_list = exec_sql("select id, cname from class", is_get_list=True)
        return render(request, "student/add_student.html", {"class_list": class_list})
    else:
        sname = request.POST.get("sname")
        class_id = request.POST.get("class_id")
        exec_sql(f'insert into student (sname, class_id) values ("{sname}","{class_id}")', is_modify=True)
        return redirect('/students/')


def edit_student(request):
    if request.method == "GET":
        sid = request.GET.get("sid")
        sname = exec_sql(f'select id,sname from student where id = {sid}', is_get_one=True)
        class_name = exec_sql(f'select cname from class where id = (select class_id from student where id = {sid})', is_get_one=True)
        class_list = exec_sql(f'select id, cname from class', is_get_list=True)
        return render(request, 'student/edit_student.html', {"sname":sname, "class_name":class_name, "class_list":class_list})
    else:
        sid = request.POST.get("sid")
        class_id = request.POST.get("class_id")
        sname = request.POST.get("sname")
        exec_sql(f'update student set sname = "{sname}", class_id={class_id} where id={sid}', is_modify=True)
        return redirect("/students/")


def del_student(request):
    sid = request.GET.get("sid")
    exec_sql(f'delete from student where id = {sid}', is_modify=True)
    return redirect("/students/")
